using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace SouthernLand.Common.DAL.Contact
{
    public class ContactDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new Contact instance filled with the DataReader's current record data 
        /// </summary>
        private BOL.Contact.Contact GetContactFromReader(IDataReader dr)
        {
            var obj = new BOL.Contact.Contact();
            obj.ContactID = (dr["ContactID"] is DBNull) ? Int32.MinValue : (Int32) dr["ContactID"];
            obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue : (Int32) dr["CateID"];
            obj.FullName = (dr["FullName"] is DBNull) ? String.Empty : (String) dr["FullName"];
            obj.Address = (dr["Address"] is DBNull) ? String.Empty : (String) dr["Address"];
            obj.Tel = (dr["Tel"] is DBNull) ? String.Empty : (String) dr["Tel"];
            obj.Email = (dr["Email"] is DBNull) ? String.Empty : (String) dr["Email"];
            obj.Title = (dr["Title"] is DBNull) ? String.Empty : (String) dr["Title"];
            obj.Content = (dr["Content"] is DBNull) ? String.Empty : (String) dr["Content"];
            obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime) dr["AddedDate"];
            obj.ViewStatus = (dr["ViewStatus"] is DBNull) ? Int32.MinValue : (Int32) dr["ViewStatus"];
            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of Contact with the data read from the input DataReader
        /// </summary>
        private List<BOL.Contact.Contact> GetContactCollectionFromReader(IDataReader dr)
        {
            var obj = new List<BOL.Contact.Contact>();
            while (dr.Read())
                obj.Add(GetContactFromReader(dr));
            return obj;
        }

        public int Insert(BOL.Contact.Contact obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Contact_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ContactID", SqlDbType.Int).Direction = ParameterDirection.Output;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = obj.FullName;
                myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
                myCommand.Parameters.Add("@Tel", SqlDbType.VarChar).Value = obj.Tel;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Content", SqlDbType.NText).Value = obj.Content;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@ViewStatus", SqlDbType.Int).Value = obj.ViewStatus;

                int ret = myCommand.ExecuteNonQuery();
                return (int) myCommand.Parameters["@ContactID"].Value;
            }
        }

        public bool Update(BOL.Contact.Contact obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Contact_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ContactID", SqlDbType.Int).Value = obj.ContactID;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
                myCommand.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = obj.FullName;
                myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
                myCommand.Parameters.Add("@Tel", SqlDbType.VarChar).Value = obj.Tel;
                myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
                myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
                myCommand.Parameters.Add("@Content", SqlDbType.NText).Value = obj.Content;
                myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
                myCommand.Parameters.Add("@ViewStatus", SqlDbType.Int).Value = obj.ViewStatus;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 ContactID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Contact_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ContactID", SqlDbType.Int).Value = ContactID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the Job_Contact with the specified ID
        /// </summary>
        public BOL.Contact.Contact GetContactByID(Int32 ContactID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Contact_GetContactByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@ContactID", SqlDbType.Int).Value = ContactID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetContactFromReader(dr) : null);
            }
        }

        //Get All Contact
        public List<BOL.Contact.Contact> GetAllContact(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Contact_GetAllContact", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetContactCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  Contact
        public int GetCountContact()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Contact_GetCountContact", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int) myCommand.ExecuteScalar();
            }
        }

        // Get ContactByCateID
        public List<BOL.Contact.Contact> GetContactByCateID(Int32 CateID, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Contact_GetContactByCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetContactCollectionFromReader(dr);
            }
        }

        // Get ContactCountByCateID
        public int GetContactCountByCateID(Int32 CateID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("Job_Contact_GetContactCountByCateID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
                return (int) myCommand.ExecuteScalar();
            }
        }
    }
}